﻿CREATE PROCEDURE [dbo].[usp_select_Faults_Messages]
(
    @Application       varchar(50)      = NULL   
   ,@StartDate         datetime         = NULL   
   ,@EndDate           datetime         = NULL   
   ,@FaultCode         varchar(50)      = NULL   
   ,@FailureCategory   varchar(50)      = NULL
   ,@ExceptionType     varchar(50)      = NULL 
   ,@MinFaultSeverity  int              = NULL
   ,@MaxFaultSeverity  int              = NULL
   ,@Debug              bit             = 0
)
AS

/****************************************************************************
Name: usp_select_Faults_Messages.sql

Description:
Select faults
a.	Selects a summary of faults to be displayed in a table on the home page.  
b.	Columns:
i.	All of the columns from the Fault table (except audit)
ii.	All of the columns from the Exception table (except audit)
iii.	Join over to the Message table, and select the all of the columns except the message body.  
This will be a second data set in the sp results.
c.	Filters:
i.	Start date and end date
ii.	Application
iii.	Minimum and Maximum fault severity (fault severity is probably 1 – 10)
iv.	Fault code
v.	Fault category
vi.	Exception type
*****************************************************************************/
SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';


-- Test harness

-- declare the stored procedure parameters
/*
DECLARE @Application       varchar(50);
DECLARE @StartDate         datetime;
DECLARE @EndDate           datetime;
DECLARE @FaultCode         varchar(50);
DECLARE @FailureCategory   varchar(50);
DECLARE @ExceptionType     varchar(50);
DECLARE @MinFaultSeverity  int;
DECLARE @MaxFaultSeverity  int;

DECLARE @Debug             bit;
-- now set them
SELECT
    @Application           = NULL    	
   ,@StartDate             = NULL    	
   ,@EndDate               = NULL    
   ,@FaultCode             = NULL       
   ,@FailureCategory       = NULL
   ,@ExceptionType         = NULL 
   ,@MinFaultSeverity      = NULL
   ,@MaxFaultSeverity      = NULL
   ,@Debug                 = 1;
*/

-- Print parameters
IF (@Debug > 0)
BEGIN
   PRINT @DebugPrefix + '@Application = ' + @Application;
   PRINT @DebugPrefix + '@StartDate = ' + CAST(@StartDate AS varchar);
   PRINT @DebugPrefix + '@EndDate = ' + CAST(@EndDate AS varchar);
   PRINT @DebugPrefix + '@FaultCode = ' + @FaultCode
   PRINT @DebugPrefix + '@FailureCategory = ' + @FailureCategory;
   PRINT @DebugPrefix + '@ExceptionType = ' + @ExceptionType;
   PRINT @DebugPrefix + '@MinFaultSeverity = ' + CAST(@MinFaultSeverity AS varchar);
   PRINT @DebugPrefix + '@MaxFaultSeverity = ' + CAST(@MaxFaultSeverity AS varchar);
END;

SELECT 
   f.[FaultID]
  ,f.[Application]
  ,f.[Description]
  ,f.[ErrorType]
  ,f.[FailureCategory]
  ,f.[FaultCode]
  ,f.[FaultDescription]
  ,f.[FaultSeverity]
  ,f.[Scope]
  ,f.[ServiceInstanceID]
  ,f.[ServiceName]
  ,f.[FaultGenerator]
  ,f.[MachineName]
  ,f.[DateTime]
  ,f.[ExceptionMessage]
  ,f.[ExceptionType]
  ,f.[ExceptionSource]
  ,f.[ExceptionTargetSite]
  ,f.[ExceptionStackTrace]
  ,f.[InnerExceptionMessage]
  --,m.[MessageID]
  --,m.[FaultID]
   --,m.[MessageName]
   --,m.[RoutingUrl]
  ,null as MessageID
  ,null as FaultID
  ,null as MessageName
  ,null as RoutingUrl

FROM dbo.Fault f --INNER JOIN dbo.[Message] m ON m.FaultID = f.FaultID
WHERE 
         f.[Application] = ISNULL(@Application, f.[Application])
   AND   f.[FailureCategory] = ISNULL(@FailureCategory, f.FailureCategory)
   AND   f.ExceptionType = ISNULL(@ExceptionType, f.ExceptionType)
   AND   f.[FaultCode] = ISNULL(@FaultCode, f.[FaultCode])
   AND   (f.[DateTime] between ISNULL(@StartDate, f.[DateTime]) AND ISNULL(@EndDate, f.[DateTime]))
   AND   (f.[FaultSeverity] between ISNULL(@MinFaultSeverity, f.[FaultSeverity]) AND ISNULL(@MaxFaultSeverity, f.[FaultSeverity]))

ORDER BY f.[DateTime] DESC

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
